package com.yc.shopping.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

public class DBHelper {
	 // 驱动类路径
    protected static String driver;
    // 连接串
    protected static String url;
    // 用户名
    protected static String user;
    // 密码
    protected static String password;
    
    //用静态块加载驱动
    static {
    	try {
    		//初始化数据库配置参数
    		Properties prop = new Properties();//Map子类
    		//获取类加载器
    		ClassLoader classLoader = DBHelper.class.getClassLoader();
    		InputStream in = classLoader.getResourceAsStream("jdbc.properties");
    		
    		if(in == null) {
    			throw new IOException("配置文件不存在！");
    		}
    		prop.load(in);
    		driver=prop.getProperty("driver");
    		url=prop.getProperty("url");
    		user=prop.getProperty("user");
    		password=prop.getProperty("password");
    		
    		
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			throw new RuntimeException("数据库驱动加载失败！",e);
		} catch (IOException e) {
			throw new RuntimeException("数据库配置文件 jdbc.properties 加载失败!",e);
		}
    }
    public static PreparedStatement sqlC(String sql,Object...params) throws SQLException {
    
    		Connection conn =getConnection();
    		PreparedStatement ps =conn.prepareStatement(sql);
    		for (int i = 0; i < params.length; i++) {
			ps.setObject(i+1, params[i]);
    	}
			return ps;
    }

    /**
     * 获取连接
     */
    public static Connection getConnection()  {

    	
		try {
			return DriverManager.getConnection(url,user,password);
		}catch(Exception e) {
			//异常转型==》异常链
			throw new RuntimeException(e);
		}
	
	
    }

    /**
     * 更新数据库,
     */
    public static int update(String sql, Object... params) throws SQLException {
    	System.out.println("SQL:"+sql);
    	System.out.println("参数:"+Arrays.toString(params));
    	try (Connection conn =getConnection()){
    		PreparedStatement ps =conn.prepareStatement(sql);
    		for (int i = 0; i < params.length; i++) {
				ps.setObject(i+1, params[i]);
			}
    		return ps.executeUpdate();
    	}
    }
    /**
     * 查询多行数据, 返回List<Map>集合
     */
    public static List<Map<String, Object>> selectListMap(String sql, Object... params) throws SQLException {
    	System.out.println("SQL:"+sql);
    	System.out.println("参数:"+Arrays.toString(params));
    	try(ResultSet rs = sqlC(sql,params).executeQuery()){
    	//返回结果集元数据对象
    	ResultSetMetaData rsmd=rs.getMetaData();
    	//返回结果集中列的数量
    	int columCount = rsmd.getColumnCount();
    	List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
    	while(rs.next()) {
    		//创建Map集合
    		Map<String,Object> row =new LinkedHashMap<String, Object>();
    		for (int i = 0; i < columCount; i++) {
				String columName = rsmd.getColumnName(i+1);
				Object columValue = rs.getObject(i+1);
				row.put(columName, columValue);
			}
    		list.add(row);
    	}
        return list;
    	}
    }

    /**
     * 查询单行数据, 返回Map集合
     */
    public static Map<String, Object> selectOneMap(String sql, Object... params) throws SQLException {
    	System.out.println("SQL:"+sql);
    	System.out.println("参数:"+Arrays.toString(params));
    	try(Connection conn =getConnection();){
    		PreparedStatement ps =conn.prepareStatement(sql,
    				ResultSet.TYPE_SCROLL_INSENSITIVE,//设置可滚动结果集
    				ResultSet.CONCUR_READ_ONLY);//设置只读
    		for (int i = 0; i < params.length; i++) {
			ps.setObject(i+1, params[i]);
    	}
    	ResultSet rs =ps.executeQuery();
    	//返回结果集元数据对象
    	ResultSetMetaData rsmd=rs.getMetaData();
    	//返回结果集中列的数量
    	int columCount = rsmd.getColumnCount();
    	
    	
    	//判断结果集数量是否正确
    	if(rs.next()==false) {
    		return null;
    	}
    	if(rs.next()==true) {
    		throw new IllegalStateException("结果集数量大于1");
    	}
    	//默认结果集不能回走
    	rs.previous();
    	
    	
    	
    	Map<String,Object> row =new LinkedHashMap<String, Object>();
    	for (int i = 0; i < columCount; i++) {
			String columName = rsmd.getColumnName(i+1);
			Object columValue = rs.getObject(i+1);
			row.put(columName, columValue);
		}
    	
        return row;
    	}
    
    }

    /**
     * 查询多行数据, 返回List<T>集合, T 为实体类对象
     */
    public static <T> List<T> selectListBean(String sql, Class<T> cls, Object... params) throws SQLException {
        return null;
    }

    /**
     * 查询单行数据, 返回T对象, T 为实体类对象
     */
    public static <T> T selectOneBean(String sql, Class<T> cls, Object... params) throws SQLException {
        return null;
    }

    /**
     * 查询分页数据
     */
    public static Page<Map<String, Object>> selectPage(String sql, int page, int size, Object... params) throws SQLException {
        //分页需要俩数据
    	//1.当前页的结果集
    	//2.总行数
    	
    	//构建分页对象
    	Page<Map<String, Object>> ret = new Page<>(page,size);
    	String pageSql = 
    			"select *\n" +
    					"  from (select a.*, rownum rn from (select * from emp) a where rownum <= ?) a\n" + 
    					" where a.rn > ?";
    	int begin =(page-1) * size;
    	int end = begin+size;
    	Object[] newParams =new Object[params.length+2];
    	//复制参数到新数组
    	System.arraycopy(params, 0, newParams, 0, params.length);
    	
    	//赋值分页参数
    	
    	newParams[params.length]= end;
    	newParams[params.length+1] = begin;
    	
    	List<Map<String, Object>> data =DBHelper.selectListMap(pageSql, newParams);
    	//查询不分页的总行数,用于计算总页数
    	String countSql = "select count(*) cnt from ("+sql+")";
    	Map<String,Object>  map =DBHelper.selectOneMap(countSql, params);
    	int rows = Integer.parseInt(""+map.get("CNT"));
    	ret.setData(data, rows);
    	return ret;
    }

    /**
     * 事务模板方法
     */
    public static <T> T execute(SqlHandler handler) {
    	Connection conn = getConnection();
    	try {
    		conn.setAutoCommit(false);
    		handler.handle(conn);
    		//如果成功则提交
			conn.commit();
			
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				throw new RuntimeException("事务回滚失败！",e);
			}
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException("关闭连接失败！",e);
			}
		}
    	
    	
        return null;
    }

    /**
     * 执行SQL语句的 函数接口
     */
    public static interface SqlHandler<T> {
        T handle(Connection connection);
    }

    public static class Page<E> {

        /**
         * 当前页数
         */
        private int page = 1;
        /**
         * 每页行数
         */
        private int size = 10;
        /**
         * 结果集总记录数
         */
        private int rows;
        /**
         * 结果集总页数
         */
        private int pages;
        /**
         * 查询到当前页的数据
         */
        private List<E> data;

        public Page(int page, int size) {
            this.page = page;
            this.size = size;
        }

        public void setData(List<E> data, int rows) {
            this.data = data;
            this.rows = rows;
            // 计算总页数
            this.pages = rows / size;
            pages += rows % size == 0 ? 0 : 1;
        }

        /**
         * 返回前后页的页码
         *
         * @return
         */
        public int nextPage() {
            return page == pages ? page : (page + 1);
        }

        /**
         * 返回前一页的页码
         *
         * @return
         */
        public int priorPage() {
            return page == 1 ? page : (page - 1);
        }

        public int getPage() {
            return page;
        }

        public int getSize() {
            return size;
        }

        public int getRows() {
            return rows;
        }

        public int getPages() {
            return pages;
        }

        public List<E> getData() {
            return data;
        }
    }

}
